﻿
''' <summary>
''' Shared Helper Class for Executing SQL Statements
''' </summary>
''' <remarks></remarks>
Public Class SqlHelper

    Shared dsParams As DataSet                  'Parameter DataSet. First table is ist of params, others are lookup
    Shared hParameterIndexes As New Hashtable   'List of Parameter Indexes
    Shared dsUnitTests As DataSet               'List of Unit Tests


    ''' <summary>
    ''' Return a Scalar Value from a SQL Server Stored Procedure
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="Parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExcecuteScalar(cmdText As String, ParamArray Parameters() As SqlClient.SqlParameter) As Object
        Using con As New SqlClient.SqlConnection(My.Settings.sqlConnectionString)
            con.Open()
            Using cmd As New SqlClient.SqlCommand(cmdText)
                If cmd.CommandText.ToLower.StartsWith("select") Then
                    cmd.CommandType = CommandType.Text
                Else
                    cmd.CommandType = CommandType.StoredProcedure
                End If
                For Each p In Parameters
                    cmd.Parameters.Add(p)
                Next
                cmd.Connection = con
                Return cmd.ExecuteScalar
            End Using
            con.Close()
        End Using
    End Function

    ''' <summary>
    ''' ExecuteScalear, Assuming that connection is already open
    ''' </summary>
    ''' <param name="con"></param>
    ''' <param name="cmdText"></param>
    ''' <param name="Parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExcecuteScalar(con As SqlClient.SqlConnection, cmdText As String, ParamArray Parameters() As SqlClient.SqlParameter) As Object
        Using cmd As New SqlClient.SqlCommand(cmdText)
            cmd.CommandType = CommandType.StoredProcedure
            For Each p In Parameters
                cmd.Parameters.Add(p)
            Next
            cmd.Connection = con
            Return cmd.ExecuteScalar
        End Using
    End Function


    ''' <summary>
    ''' Execuet SQL Procedure and return Nothing
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="Parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExcecuteNonQuery(cmdText As String, ParamArray Parameters() As SqlClient.SqlParameter) As Integer
        Using con As New SqlClient.SqlConnection(My.Settings.sqlConnectionString)
            con.Open()
            Using cmd As New SqlClient.SqlCommand(cmdText)
                cmd.CommandType = CommandType.StoredProcedure
                For Each p In Parameters
                    cmd.Parameters.Add(p)
                Next
                cmd.Connection = con
                Return cmd.ExecuteNonQuery
            End Using
            con.Close()
        End Using
    End Function




    Public Shared Function ExecuteDataTable(cmdText As String) As DataTable
        Dim ds As New DataSet
        Using con As New SqlClient.SqlConnection(My.Settings.sqlConnectionString)
            con.Open()
            ds = ExcecuteDataSet(con, cmdText, Nothing)
            Return ds.Tables(0)
            con.Close()
        End Using
    End Function


    ''' <summary>
    ''' Execute a DataSet and return a DataSet
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="Parameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExcecuteDataSet(con As SqlClient.SqlConnection, cmdText As String, ParamArray Parameters() As SqlClient.SqlParameter) As DataSet
        Dim ds As New DataSet("DataSet")
        Dim autoClose As Boolean = True
        If con.State = ConnectionState.Closed Then con.Open()

        Using cmd As New SqlClient.SqlCommand(cmdText)
            If Left(cmdText, 6).ToLower = "select" Then
                cmd.CommandType = CommandType.Text
            Else
                cmd.CommandType = CommandType.StoredProcedure
            End If

            If Not (Parameters Is Nothing) Then
                For Each p In Parameters
                    cmd.Parameters.Add(p)
                Next
            End If
            cmd.Connection = con
            Using da As New SqlClient.SqlDataAdapter(cmd)
                da.Fill(ds)
                Return ds
            End Using

            con.Close()
            con.Dispose()
        End Using

    End Function

    Public Shared Sub TestConnection()
        Dim cmdText As String = String.Format("Select top 1 * from {0}", My.Settings.sqlTable)
        Using con As New SqlClient.SqlConnection(My.Settings.sqlConnectionString)
            con.Open()
            Using ds As DataSet = ExcecuteDataSet(con, cmdText)
                If Not ds.Tables(0).Columns.Contains("TextData") Then Throw New Exception(String.Format("Table {0} does must contain column [TextData]", My.Settings.sqlTable))
                If Not ds.Tables(0).Columns.Contains("RequestParameters") Then Throw New Exception(String.Format("Table {0} does must contain column [RequestParameters]", My.Settings.sqlTable))
            End Using
            con.Close()
        End Using


    End Sub


End Class
